package dao.impl;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import model.Grid;
import model.MyPoint;
import utils.UtilJdbc;

public class GridDaoImpl  {
	
	public void add(Grid grid) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = UtilJdbc.getConnection();
			String sql = "insert into grid(gridName) values(?)";
			st = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);

			String utfName= new String(grid.getGridName().getBytes(), "UTF-8");  
			st.setString(1, utfName);
			st.executeUpdate();
			 rs = st.getGeneratedKeys();  
	        if (rs.next()) {  
	            System.out.println(rs.getLong(1));  
	            grid.setId(rs.getLong(1));
	        }  

		}catch (Exception e) {
			throw new RuntimeException(e);
		}finally{
			UtilJdbc.release(conn, st, rs);
		}
		addPointList(grid);
		
	}
	
	public void addPointList(Grid grid){
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = UtilJdbc.getConnection();
			String sql = "insert into MyPoint(x,y,gridId,seq) values(?,?,?,?)";
			st = conn.prepareStatement(sql);
			long seq=0;
			for(MyPoint point:grid.getPointList()){
				st.setDouble(1,point.getX());
				st.setDouble(2,point.getY());
				st.setLong(3, grid.getId());
				st.setLong(4, seq);
				st.addBatch();
			}
			st.executeBatch();

		}catch (Exception e) {
			throw new RuntimeException(e);
		}finally{
			UtilJdbc.release(conn, st, rs);
		}
	}
	
	
	public List<Grid> getAll(){
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		List<Grid> list=new ArrayList<>();
		try{
			conn = UtilJdbc.getConnection();
			
			String sql = "select * from grid ";
			st = conn.prepareStatement(sql);
			
			rs = st.executeQuery();
			while(rs.next()){
				Grid grid=new Grid();
				grid.setCity(rs.getString("city"));
				grid.setId(rs.getLong("id"));
				grid.setGridName(rs.getString("gridName"));
				list.add(grid);
			}
			//
			for(Grid g:list){
				List<MyPoint> pointList=getPointList(g.getId());
				g.setPointList(pointList);
			}
			
			return list;
		}catch (Exception e) {
			throw new RuntimeException(e);
		}finally{
			UtilJdbc.release(conn, st, rs);
		}
	}
	
	public List<MyPoint> getPointList(Long gridId){
		List<MyPoint> pointList=new ArrayList<>();
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = UtilJdbc.getConnection();
			
			String sql = "select * from MyPoint where gridId=? order by seq";
			st = conn.prepareStatement(sql);
			st.setLong(1, gridId);
			
			rs = st.executeQuery();
			while(rs.next()){
				MyPoint point=new MyPoint();
				point.setX(rs.getDouble("x"));
				point.setY(rs.getDouble("y"));
				pointList.add(point);
			}
			//
			
			return pointList;
		}catch (Exception e) {
			throw new RuntimeException(e);
		}finally{
			UtilJdbc.release(conn, st, rs);
		}
	}
	//username = ' or 1=1 or username='  java--class
	/*public User find(String username, String password) {
		
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			
			String sql = "select * from users where username=? and password=?";
			st = conn.prepareStatement(sql);
			st.setString(1, username);
			st.setString(2, password);
			
			rs = st.executeQuery();  //
			if(rs.next()){
				User user = new User();
				user.setId(rs.getString("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setBirthday(rs.getDate("birthday"));
				return user;
			}
			return null;
		}catch (Exception e) {
			throw new DaoException(e);
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}

	public User find(String username) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtils.getConnection();
			String sql = "select * from users where username=?";
			st = conn.prepareStatement(sql);
			st.setString(1, username);
			
			rs = st.executeQuery();
			if(rs.next()){
				User user = new User();
				user.setId(rs.getString("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setBirthday(rs.getDate("birthday"));
				return user;
			}
			return null;
		}catch (Exception e) {
			throw new DaoException(e);
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}*/

}
